﻿<%@ WebHandler Language="C#" Class="GZCNCSpecDashBoardV2" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 工装工具厂--工序能力（无工时）看板
 * Copyright(c): 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.6.25
 */

public class GZCNCSpecDashBoardV2 : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getSpecPieChart":
                        r = getSpecPieChart(jObject);//各数控工序占比--饼图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSpecBarChart":
                        r = getSpecPieChart(jObject);//各数控工序统计--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenterSpecBarChart":
                        r = getWorkcenterSpecBarChart(jObject);//各工区数控序统计--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getData":
                        r = getData(jObject);//页面主页面表格
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetails":
                        r = getData(jObject);//数据钻取
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    case "getSpecItems":
                        r = getSpecItems(jObject);//查询已维护工序项
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "checkIsExist":
                        r = checkIsExist(jObject);//检查待新增工序是否已存在
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "insertData":
                        r = insertData(jObject);//新增工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deleteData":
                        r = deleteData(jObject);//删除工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "changeStatus":
                        r = changeStatus(jObject);//切换启用状态
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "updateData":
                        r = updateData(jObject);//修改工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSql(JObject jObject)
    {
        string sql = @"
SELECT gt.containername topcontainer,sub.subproductname,c.containername,c.qty,c.fromworkcentername workcentername,
       ws.workflowstepname,s.opdesc,s.specno,gt.ordertype,gt.plantype,gt.startdate,gt.enddate,
       CASE WHEN TRUNC(SYSDATE) >= gt.enddate THEN TRUNC(SYSDATE) - gt.enddate || '天' ELSE '否' END delaystatus,
       CASE WHEN pp.isallset = '否' THEN '否' ELSE '是' END isallset,
       pp.allsetplandate,pp.missingpartsnotes,pp.notes,
       csr.dispatchemployeename,csr.dispatchdate,csr.reportemployeename,csr.reportdate,csr.checkemployeename,csr.checkdate,
       CASE WHEN csr.checkdate IS NOT NULL THEN '已完工'
            WHEN csr.reportdate IS NOT NULL THEN '待检验'
            WHEN csr.dispatchdate IS NOT NULL THEN '待报工'
            WHEN csr.dispatchdate IS NULL THEN '待派工' 
       END status
FROM gzordertypemanagement gt
LEFT JOIN containercurrentinfo cci ON cci.containername = gt.containername
LEFT JOIN 
(
   SELECT DISTINCT b.subproductname,b.topproductname
   FROM bomproductsubproduct b
   START WITH b.productname IN (SELECT DISTINCT m.productname 
                                FROM gzordertypemanagement m
                                WHERE m.enddate >= to_date('{0}','yyyy/mm/dd') AND m.enddate <= to_date('{1}','yyyy/mm/dd')
                               )
   CONNECT BY PRIOR b.subproductname = b.productname
) sub ON sub.topproductname = gt.productname
LEFT JOIN containercurrentinfo c ON c.productname = sub.subproductname AND c.containerno = cci.containerno
LEFT JOIN workflowstep ws ON ws.workflowid = NVL(c.bsworkflowid,c.workflowid)
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
INNER JOIN gzcncspecmaint ncs ON ncs.specname = s.opdesc AND ncs.status = 1
LEFT JOIN gzpreparemanagement pp ON pp.containername = c.containername
LEFT JOIN containersteprecord csr ON csr.containerid = c.containerid AND csr.workflowstepname = ws.workflowstepname
WHERE gt.enddate >= to_date('{0}','yyyy/mm/dd') AND gt.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (jObject["opdesc"] != null && jObject["opdesc"].ToString() != "")
        {
            sql += string.Format(" AND s.opdesc='{0}'", jObject["opdesc"].ToString());
        }
        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND c.fromworkcentername='{0}'", jObject["workcentername"].ToString());
        }

        sql += @"

UNION ALL

SELECT gt.containername topcontainer,cci.productname subproductname,cci.containername,cci.qty,cci.fromworkcentername workcentername,
       ws.workflowstepname,s.opdesc,s.specno,gt.ordertype,gt.plantype,gt.startdate,gt.enddate,
       CASE WHEN TRUNC(SYSDATE) >= gt.enddate THEN TRUNC(SYSDATE) - gt.enddate || '天' ELSE '否' END delaystatus,
       CASE WHEN pp.isallset = '否' THEN '否' ELSE '是' END isallset,
       pp.allsetplandate,pp.missingpartsnotes,pp.notes,
       csr.dispatchemployeename,csr.dispatchdate,csr.reportemployeename,csr.reportdate,csr.checkemployeename,csr.checkdate,
       CASE WHEN csr.checkdate IS NOT NULL THEN '已完工'
            WHEN csr.reportdate IS NOT NULL THEN '待检验'
            WHEN csr.dispatchdate IS NOT NULL THEN '待报工'
            WHEN csr.dispatchdate IS NULL THEN '待派工' 
       END status
FROM gzordertypemanagement gt
LEFT JOIN containercurrentinfo cci ON cci.containername = gt.containername
LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
INNER JOIN gzcncspecmaint ncs ON ncs.specname = s.opdesc AND ncs.status = 1
LEFT JOIN gzpreparemanagement pp ON pp.containername = cci.containername
LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid AND csr.workflowstepname = ws.workflowstepname
WHERE gt.enddate >= to_date('{0}','yyyy/mm/dd') AND gt.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (jObject["opdesc"] != null && jObject["opdesc"].ToString() != "")
        {
            sql += string.Format(" AND s.opdesc='{0}'", jObject["opdesc"].ToString());
        }
        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcentername='{0}'", jObject["workcentername"].ToString());
        }
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        sql = string.Format(sql, selectdate[0].ToString(), selectdate[1].ToString());
        return sql;
    }


    //各数控工序占比--饼图
    Result getSpecPieChart(JObject jObject)
    {
        string sql = @"SELECT t.opdesc,COUNT(t.workflowstepname) qty FROM ({0}) t GROUP BY t.opdesc";
        sql = string.Format(sql, getSql(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各工区数控序统计--柱状图
    Result getWorkcenterSpecBarChart(JObject jObject)
    {
        string sql = @"SELECT t.workcentername,COUNT(t.workflowstepname) qty FROM ({0}) t GROUP BY t.workcentername";
        sql = string.Format(sql, getSql(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //页面主页面表格
    Result getData(JObject jObject)
    {
        string sql = getSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("topcontainer");
        dtResult.Columns.Remove("opdesc");
        dtResult.Columns.Remove("specno");
        dtResult.Columns.Remove("notes");

        dtResult.Columns["subproductname"].Caption = "零件号";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workcentername"].Caption = "责任工区";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["ordertype"].Caption = "订单类型";
        dtResult.Columns["plantype"].Caption = "计划类型";
        dtResult.Columns["startdate"].Caption = "计划开始";
        dtResult.Columns["enddate"].Caption = "计划完成";
        dtResult.Columns["delaystatus"].Caption = "是否超期";
        dtResult.Columns["isallset"].Caption = "是否齐套";
        dtResult.Columns["allsetplandate"].Caption = "预齐套时间";
        dtResult.Columns["missingpartsnotes"].Caption = "缺件情况";
        dtResult.Columns["dispatchemployeename"].Caption = "派工人";
        dtResult.Columns["dispatchdate"].Caption = "派工时间";
        dtResult.Columns["reportemployeename"].Caption = "报工人";
        dtResult.Columns["reportdate"].Caption = "报工时间";
        dtResult.Columns["checkemployeename"].Caption = "检验员";
        dtResult.Columns["checkdate"].Caption = "检验时间";
        dtResult.Columns["status"].Caption = "状态";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + "工序能力（无工时）看板导出数据");
    }

    //查询已维护工序项
    Result getSpecItems(JObject jObject)
    {
        string sql = @"
SELECT t.id,t.specname,t.status,e.fullname createemployee,t.createdate,e2.fullname lastchangeemployee,t.lastchangedate
FROM gzcncspecmaint t
LEFT JOIN employee e ON e.employeeid = t.createemployee
LEFT JOIN employee e2 ON e2.employeeid = t.lastchangeemployee
WHERE 1=1";
        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND t.specname LIKE '%{0}%'", jObject["specname"].ToString());
        }
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //校验是否已存在
    Result checkIsExist(JObject jObject)
    {
        string sql = @"SELECT * FROM gzcncspecmaint t WHERE t.specname = '{0}'";
        if (jObject["id"] != null && jObject["id"].ToString() != "")
        {
            sql += string.Format(" AND t.id <> '{0}'", jObject["id"].ToString());
        }
        sql = string.Format(sql, jObject["specname"].ToString().Trim());
        DataTable dt = dal.GetDataTable(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //新增数据
    Result insertData(JObject jObject)
    {
        string sql = @"INSERT INTO gzcncspecmaint(id,specname,status,createemployee,createdate) VALUES('{0}','{1}',1,'{2}',sysdate)";
        sql = string.Format(sql,
                            Guid.NewGuid().ToString(),//唯一id
                            jObject["specname"].ToString().Trim(),//工序名称
                            jObject["employeeid"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //删除数据
    Result deleteData(JObject jObject)
    {
        string sql = string.Format("DELETE gzcncspecmaint t WHERE t.id = '{0}'", jObject["id"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //切换启用状态
    Result changeStatus(JObject jObject)
    {
        string sql = @"UPDATE gzcncspecmaint t SET t.status='{1}',t.lastchangedate = sysdate,t.lastchangeemployee='{2}' WHERE t.id='{0}'";
        int status = 0;
        if (jObject["status"] != null)
        {
            status = jObject["status"].ToString() == "True" ? 1 : 0;
        }

        sql = string.Format(sql, jObject["id"].ToString(), status, jObject["employeeid"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //修改工序
    Result updateData(JObject jObject)
    {
        string sql = @"
UPDATE gzcncspecmaint t 
SET t.specname = '{1}',t.lastchangeemployee = '{2}',t.lastchangedate = sysdate
WHERE t.id = '{0}'";

        sql = string.Format(sql,
                            jObject["id"].ToString(),//唯一id
                            jObject["specname"].ToString().Trim(),//工序名称
                            jObject["employeeid"].ToString()//修改人
                            );
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}